﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using TRL.TRLAdmin;
using System.IO;
using System.Text;


namespace TRL.TRLAdmin.Members
{
    public partial class ViewMembersNeedingWelcomeKits : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
                bindMembershipsTableToGrid();
        }

        /// <summary>
        /// Beinds the membership table to the grid. We could use Linq to SQL or Linq to Entities or
        /// some other less code intensive way, but I miss the control of SqlConnection \ SqlCommand
        /// so I'm using that instead.
        /// </summary>
        private void bindMembershipsTableToGrid()
        {
            MembershipGrid.DataSource = getMembershipsDataTable();
            MembershipGrid.DataBind();

            if (MembershipGrid.Rows.Count > 0)
            {
                //This replaces <td> with <th> and adds the scope attribute
                MembershipGrid.UseAccessibleHeader = true;

                //This will add the <thead> and <tbody> elements
                MembershipGrid.HeaderRow.TableSection = TableRowSection.TableHeader;

                //This adds the <tfoot> element. 
                //Remove if you don't have a footer row
                MembershipGrid.FooterRow.TableSection = TableRowSection.TableFooter;
            }
        }

        /// <summary>
        /// Creates and returns a DataTable object of Membership needing welcome kits.
        /// Should consider combining this with the getMembershipsDataTable function in ViewMembers
        /// page.
        /// </summary>
        /// <returns>A DataTable containing the Memberships table</returns>
        private DataTable getMembershipsDataTable()
        {
            string strSQLconnection = ConfigurationManager.ConnectionStrings["trladminConnectionString"].ToString();
            using (var sqlConnection = new SqlConnection(strSQLconnection))
            {
                var sqlCommandStr = @"
                    SELECT 
                        [membershipId]
                        ,mt.[typeName] AS membershipType
                        ,CONVERT(CHAR(10), [membershipExpiration], 101) AS membershipExpiration
                        ,[firstName]
                        ,[lastName]
                        ,[secondFirstName]
                        ,[secondLastName]
                        ,[address]
                        ,[city]
                        ,[state]
                        ,[postalCode]
                        ,(CASE 
                            WHEN phoneNumber LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
                            THEN LEFT(phoneNumber,3)+'.'+SUBSTRING(phoneNumber,4,3)+'.'+RIGHT(phoneNumber,4) 
                            ELSE phoneNumber 
                        END) AS phoneNumber
                        ,[email]
                        ,[secondEmail]    
                        ,[forumUsername]
                        ,CONVERT(CHAR(10), [memberSince], 101) AS memberSince
                        ,[notes]
                        ,(CASE WHEN ISNULL(sendPaperNewsletter,1)= 1 THEN 'Y' ELSE 'N' END) AS PaperNewsletter
                        ,(CASE WHEN ISNULL(sendENewsletter,1) = 1 THEN 'Y' ELSE 'N' END) AS ENewsletter
                        ,(CASE WHEN ISNULL(okayToContact,1) = 1 THEN 'Y' ELSE 'N' END) AS Contact
                    FROM Memberships m
                    INNER JOIN MembershipTypes mt ON
                        mt.membershipTypeId = m.membershipType
                    WHERE
                        needWelcomeKit = 1
                    ORDER BY membershipExpiration DESC
                    ";
                var sqlCommand = new SqlCommand();
                sqlCommand.Connection = sqlConnection;
                sqlCommand.CommandText = sqlCommandStr;
                sqlConnection.Open();
                var reader = sqlCommand.ExecuteReader();
                var dt = new DataTable();
                dt.Load(reader);
                return dt;
            }
        }
    }

}


